//Comment to check version revision and to check documentation of changes
import java.sql.*;
import java.io.*;
import java.util.*;
import java.lang.String;

/******************************************************************************
* The <code>InventoryDB</code> class stores Inventory objects in a database.
* Methods are provided to access, modify, and delete the data.
* @author John Phillips modified by Casey Douglas
* @version 1.1.0
*****************************************************************************/
public class InventoryDB extends DB
{

    Scanner keyboard = new Scanner(System.in);
    ResultSet rs = null;

    /***************************************************************************
    * Creates an <code>InventoryDB</code> object
    **************************************************************************/
    public InventoryDB()
    {
     	super();
    }

    /***************************************************************************
    * Adds a new product to the system.
    * @param name A <code>String</code> stores the Product Name.
    * @param size A <code>String</code> stores the size of the product.
    * @param price A <code>double</code> stores the price of the product.
    * @param amount an <code>int</code> store the amount in stock.
    **************************************************************************/
    public void addProduct()
    {
      	System.out.print("Please enter in the product name: ");
        String name = keyboard.nextLine();
       	System.out.print("Please enter in the product size: ");
       	String size = keyboard.nextLine();

        double price = Validator2.getDouble(keyboard, "Please enter in the price of the product: ");
        int amount = Validator2.getInt(keyboard, "Please enter in the quantity of this product: ");

       	String q = "insert into inventory values";
       	q+= "(null" + ",\"" + name + "\", \"" + size + "\", \"" + price + "\", \"" + amount + "\" )";
       	//System.out.println("q=" + q); //for debugging
       	doUpdate( q );
    }

    /***************************************************************************
    * Deletes a product from the database.
    * @param num An <code>int</code> stores the ID of the product to be deleted.
    * @param
    **************************************************************************/
    public void deleteProduct()
    {

       	rs = getProducts();

       	int num = Validator2.getInt(keyboard, "Please enter the ID number you wish to delete: ");

       	System.out.print("Are you sure you want to delete this item? (Y/N): ");
        String choice = keyboard.next();
       	choice = choice.toLowerCase();

       	if(choice.equals("y"))
       	{
         	String q = "delete from inventory where productId = " + num;
           	doUpdate( q );
       	}
       	keyboard.nextLine();
 	}

   	/**********************************************************************
    * Allows a user to edit an entry in the database.
    * @param ID an <code>int</code> stores the product ID number.
    * @param name a <code>string</code> stores the product name.
    * @param size a <code>string</code> stores the size of the product.
    * @param price a <code>double</code> stores the price of th eproduct.
    * @param amoubnt an <code>int</code> stores the amount in stock.
    **********************************************************************/
    public void editProduct()
    {
        rs = getProducts();

        int ID = Validator2.getInt(keyboard, "Please enter the product ID you would like to edit: ");
       	System.out.print("Please Enter new name of: ");
        String name = keyboard.next();
       	System.out.print("Please Enter the new size: ");
        String size = keyboard.next();
        double price = Validator2.getDouble(keyboard, "Please enter the new price: ");
        int amount = Validator2.getInt(keyboard, "Please enter the new quantity: ");

       	String q = "Update inventory Set productName = \"" +  name + "\", productSize = \"" + size +
            "\", price = \"" + price + "\", quantity = \"" + amount + "\" where productId = \"" + ID + "\"";
       	//System.out.println(q); //this is for debugging
       	doUpdate(q);
    }

    /***********************************************************************
    * Allows the user to search by Brandname.
    * @param brand a <code>String</code> stores the brand to search for.
    * @return rs a <code>ResultSet</code> object that has all
    *       products with name specified.
    * @param q a <code>string</code> contains query statement.
    ***********************************************************************/
	public ResultSet searchProducts()
    {
		ResultSet rs = null;

		System.out.print("Please enter in the brand name you wish to see: ");
		String brand = keyboard.next();
        keyboard.nextLine();
		String q = "select * from inventory where productName = \"" + brand + "\"";

        try {
		    rs = doQuery(q);

			while( rs.next()) {
			    System.out.println(rs.getInt(1) + "\t" + rs.getString(2) + "\t" + rs.getString(3) + "\t"
				    + rs.getDouble(4) + "\t" + rs.getString(5));
		  	}
		}
		catch (SQLException e) {
			System.out.println("SQL Exception on searchProducts method of InventoryDB class.");
		}
     	return rs;
	}

    /***************************************************************************
    * Gets all products in database.
    * @param q A <code>String</code> contains mysql statement.
    * @return rs A <code>ResultSet</code> object.
    **************************************************************************/
    public ResultSet getProducts()
    {
		String q = "select * from inventory order by productId";
		ResultSet rs = null;

      	System.out.println("ID\tName\t\tSize\tPrice\tQuantity");

		try {
			rs = doQuery(q);

			while( rs.next()) {
				System.out.println(rs.getInt(1) + "\t" + rs.getString(2) + "\t\t" + rs.getString(3) + "\t"
				    + rs.getDouble(4) + "\t" + rs.getInt(5));
			}
		}
		catch (SQLException e){
			System.out.println("SQL Exception in getProducts method of InventoryDB class.");
		}
		catch (Exception e){
			e.printStackTrace();
		}
       	return rs;
    }

	/***********************************************************************************
    * Displays a list of products sorted by price.
    * @param q a <code>string</code> contains a mysql query statement.
    * @return rs a <code>ResultSet</code> object containing products.
    ***********************************************************************************/
   	public  ResultSet priceSort()
    {
       	String q = "select * from inventory order by price";
       	ResultSet rs = null;
        try{
            rs = doQuery(q);

           	while(rs.next())
           	{
               	System.out.println(rs.getInt(1) + "\t" + rs.getString(2) + "\t" + rs.getString(3) + "\t" +
                   	rs.getDouble(4) + "\t" + rs.getString(5));
           	}
        }
        catch(SQLException e){
       		System.out.println("Problem in priceSort");
        }
        return rs;
    }

    /*****************************************************************************************
    * Displays a list of products sorted by quantity.
    * @param q a <code>string</code> contains a mysql query statement.
    * @return rs a <code>ResultSet</code> object containing products.
    *****************************************************************************************/
   	public ResultSet quantitySort()
   	{
       	String q = "select * from inventory order by quantity";
       	ResultSet rs = null;
       	try{
       		rs = doQuery(q);

       		while(rs.next())
       		{
               	System.out.println(rs.getInt(1) + "\t" + rs.getString(2) + "\t" + rs.getString(3) + "\t" +
                   	rs.getDouble(4) + "\t" + rs.getInt(5));
       		}
       	}
       	catch(SQLException e){
       		System.out.println("Problem in quantitySort");
       	}
       	return rs;
    }

    /*****************************************************************************************
    * Calculates the amount of a product a user purchases.
    * @param id an <code>int</code> stores the product id #.
    * @param amount an <code>int</code> stores the amount the user wishes to buy.
    * @param stock an <code>int</code> stores the amount of the product in stock.
    * @param price a <code>double</code> stores the price of the product.
    * @param total a <code>double</code> stores the total sale price.
    * @param choice a <code>string</code> stores the option of either y/n.
    *****************************************************************************************/
   	public void calc()
   	{
       	getProducts();

        int id = Validator2.getInt(keyboard, "Please select the product ID you wish to purchase: ");
        int amount = Validator2.getInt(keyboard, "Please enter in the quantity you wish to purchase: ");

       	int stock = 0;
       	double price = 0;
       	String q = "select price, quantity from inventory where productId = \"" + id + "\"";
       	try{
       		rs = doQuery(q);
       		while(rs.next())
       		{
           		price = rs.getDouble(1);
           		stock = rs.getInt(2);
       		}
       	}
        catch(SQLException e){
       		System.out.println("Problem in calc method.....still.");
       	}

       	if(amount <= stock)
       	{
       		double total = amount * price;
       		System.out.println("Your total is: " + total);

       		System.out.print("Do you wish to complete the purchase? (Y/N): ");
           	String choice = keyboard.next();
       		choice = choice.toLowerCase();

       		if(choice.equals("y"))
       		{
           		int leftover = stock - amount;
           		q = "update inventory set quantity = \"" + leftover + "\" where productId = \"" + id + "\"";
           		doUpdate(q);
       		}
       	}
       	else
       	{
       		System.out.println("There is not enough in stock.");
       	}
   	}

    /***************************************************************************************
    * Allows the user to add inventory by inputing a file.
    * @param filename a <code>string</code> stores the name of the file to add products from
    * @param inputString a <code>string</code> stores the information from the textfile.
    * @param inputArray[] a <code>string array</code> stores the a peice of information
    *       in each of it's slots.
    * @param name a <code>string</code> stores the product name.
    * @param size a <code>string</code> stores the product size.
    * @param price a <code>double</code> stores the price of the product.
    * @param amount an <code>int</code> stores the amount in stock.
    * @param q a <code>string</code>  contains a mysql query statement.
    ***************************************************************************************/
	public void BatchAdd() throws FileNotFoundException, IOException
	{
		try
		{
		    System.out.print("What is the file to be added: ");
		    String fileName = keyboard.next();

		    File productFile = new File(fileName);
		    BufferedReader input = new BufferedReader(new FileReader(productFile));

		    String inputString = input.readLine();

		    while(inputString != null)
            {
			    String inputArray[] = inputString.split(",");
			    String name = inputArray[0];
			    String size = inputArray[1];
			    double price = Double.parseDouble(inputArray[2]);
            	int amount = Integer.parseInt(inputArray[3]);

		    	String q = "insert into inventory values";
		        q+= "(null" + ",\"" + name + "\", \"" + size + "\", \"" + price + "\", \"" + amount + "\" )";
                //System.out.println("q=" + q); //this is for debugging
            	doUpdate( q );

            	inputString = input.readLine();
		    }
		    input.close();
		}catch (Exception e){

	    }
    }

    /***************************************************************************
    * Displays the choices the user has and calls other methods depending on
    *       what choice is made by the user.
    * @param keyboard a <code>scanner</code> object.
    * @param rs a <code>ResultSet</code> object.
    * @param choice an <code>int</code> stores the choice made.
    **************************************************************************/
    public static void main(String args[])
	{

	   	Scanner keyboard = new Scanner(System.in);
	   	InventoryDB invtDB = new InventoryDB();

    	ResultSet rs = null;
    	int choice;

    	do
    	{
    		System.out.println("0. Quit");
    		System.out.println("1. Add Product");
    		System.out.println("2. Delete Product");
    		System.out.println("3. Edit Product");
    		System.out.println("4. Search By Brand");
    		System.out.println("5. Display All Products");
    		System.out.println("6. Sort by Price");
    		System.out.println("7. Sort by Quantity");
    		System.out.println("8. Calculation");
    		System.out.println("9. Add Batch File");
    		System.out.println();

    		choice = Validator2.getIntWithinRange(keyboard, "Please Pick an option: ", -1, 10);

    		switch(choice)
    		{
    			case 0:
    				break;
    			case 1:
    				invtDB.addProduct();
    				break;
    			case 2:
              		invtDB.deleteProduct();
    				break;
    			case 3:
               		invtDB.editProduct();
    				break;
    			case 4:
    				rs = invtDB.searchProducts();
    				break;
    			case 5:
    				rs = invtDB.getProducts();
    				break;
    			case 6:
              		rs = invtDB.priceSort();
    				break;
    			case 7:
               		rs = invtDB.quantitySort();
    				break;
    			case 8:
               		invtDB.calc();
    				break;
    			case 9:
    				try{invtDB.BatchAdd();}catch(Exception e){}
    				break;
    			default:
    				System.out.println("Please choose one of the available options");
    				break;
    		}
    		System.out.println();
    	}
    	while(choice != 0);
   	}
}
